import { query, transaction } from '../config/database.js'
import { nanoid } from 'nanoid'

export class Leave {
  /**
   * 创建请假申请
   * @param {Object} leaveData
   * @returns {Promise<Object>}
   */
  static async create({ studentId, type, startAt, endAt, reason }) {
    return await transaction(async (connection) => {
      // 计算请假时长
      const hours = Math.max(0, Math.round((new Date(endAt) - new Date(startAt)) / 36e5))

      // 插入请假记录
      const [result] = await connection.execute(
        `INSERT INTO leaves (student_id, type, start_at, end_at, hours, reason, status)
         VALUES (?, ?, ?, ?, ?, ?, 'pending')`,
        [studentId, type, startAt, endAt, hours, reason],
      )

      const leaveId = result.insertId
      const now = new Date().toISOString().slice(0, 19).replace('T', ' ')

      // 创建审批步骤1：提交申请
      await connection.execute(
        `INSERT INTO approval_steps (id, leave_id, title, comment, status, acted_by, acted_at, step_order)
         VALUES (?, ?, ?, ?, 'approved', ?, ?, 1)`,
        [nanoid(), leaveId, '提交申请', '申请已提交', studentId, now],
      )

      // 创建审批步骤2：老师审批
      await connection.execute(
        `INSERT INTO approval_steps (id, leave_id, title, comment, status, acted_by, acted_at, step_order)
         VALUES (?, ?, ?, ?, 'pending', NULL, NULL, 2)`,
        [nanoid(), leaveId, '老师审批', '待处理'],
      )

      return this.findByIdWithSteps(leaveId)
    })
  }

  /**
   * 根据ID查找请假申请（包含审批步骤）
   * @param {number} id
   * @returns {Promise<Object|null>}
   */
  static async findByIdWithSteps(id) {
    const sql = `
      SELECT l.*, u.name as student_name, u.username as student_username
      FROM leaves l
      LEFT JOIN users u ON l.student_id = u.id
      WHERE l.id = ?
    `
    const rows = await query(sql, [id])
    if (!rows.length) return null

    const leave = rows[0]

    // 获取审批步骤
    const stepsSql = `
      SELECT s.*, u.name as actor_name
      FROM approval_steps s
      LEFT JOIN users u ON s.acted_by = u.id
      WHERE s.leave_id = ?
      ORDER BY s.step_order
    `
    const steps = await query(stepsSql, [id])

    leave.steps = steps
    return leave
  }

  /**
   * 查询请假申请列表（支持分页、筛选、排序）
   * @param {Object} options
   * @returns {Promise<Object>}
   */
  static async findAll({
    studentId,
    status,
    type,
    page = 1,
    limit = 10,
    sortBy = 'created_at',
    order = 'DESC',
  }) {
    // 确保 page 和 limit 是数字
    const safeLimit = Number(limit) || 10
    const safePage = Number(page) || 1
    const offset = (safePage - 1) * safeLimit

    const conditions = []
    const params = []

    if (studentId) {
      conditions.push('l.student_id = ?')
      params.push(Number(studentId))
    }

    if (status) {
      conditions.push('l.status = ?')
      params.push(status)
    }

    if (type) {
      conditions.push('l.type = ?')
      params.push(type)
    }

    const whereClause = conditions.length ? 'WHERE ' + conditions.join(' AND ') : ''

    // 安全的排序字段白名单
    const allowedSortFields = ['created_at', 'start_at', 'end_at', 'hours', 'status', 'type']
    const safeSortBy = allowedSortFields.includes(sortBy) ? sortBy : 'created_at'
    const safeOrder = order.toUpperCase() === 'ASC' ? 'ASC' : 'DESC'

    // 查询总数
    const countSql = `SELECT COUNT(*) as total FROM leaves l ${whereClause}`
    const countResult = await query(countSql, params)
    const total = countResult[0]?.total || 0

    // 查询列表 - 使用字符串拼接而不是参数化（LIMIT/OFFSET 在 MySQL2 中可能有问题）
    const listSql = `
      SELECT l.*, u.name as student_name, u.username as student_username
      FROM leaves l
      LEFT JOIN users u ON l.student_id = u.id
      ${whereClause}
      ORDER BY l.${safeSortBy} ${safeOrder}
      LIMIT ${safeLimit} OFFSET ${offset}
    `
    const list = await query(listSql, params)

    return {
      list,
      total,
      page: safePage,
      limit: safeLimit,
      totalPages: Math.ceil(total / safeLimit),
    }
  }

  /**
   * 更新请假申请状态
   * @param {number} id
   * @param {string} status
   * @returns {Promise<boolean>}
   */
  static async updateStatus(id, status) {
    const sql = 'UPDATE leaves SET status = ? WHERE id = ?'
    const result = await query(sql, [status, id])
    return result.affectedRows > 0
  }

  /**
   * 审批请假申请
   * @param {number} leaveId
   * @param {number} teacherId
   * @param {string} action 'approve' or 'reject'
   * @param {string} comment
   * @returns {Promise<Object>}
   */
  static async approve(leaveId, teacherId, action, comment) {
    return await transaction(async (connection) => {
      const newStatus = action === 'approve' ? 'approved' : 'rejected'
      const now = new Date().toISOString().slice(0, 19).replace('T', ' ')

      // 更新请假申请状态
      await connection.execute('UPDATE leaves SET status = ? WHERE id = ?', [newStatus, leaveId])

      // 更新审批步骤
      await connection.execute(
        `UPDATE approval_steps
         SET status = ?, comment = ?, acted_by = ?, acted_at = ?
         WHERE leave_id = ? AND step_order = 2`,
        [newStatus, comment, teacherId, now, leaveId],
      )

      return this.findByIdWithSteps(leaveId)
    })
  }

  /**
   * 获取特定教师审批过的请假申请列表（支持分页、筛选、排序）
   * @param {Object} options
   * @returns {Promise<Object>}
   */
  static async findAllByTeacher({
    teacherId,
    status,
    page = 1,
    limit = 10,
    sortBy = 'updated_at',
    order = 'DESC',
  }) {
    // 确保 page 和 limit 是数字
    const safeLimit = Number(limit) || 10
    const safePage = Number(page) || 1
    const offset = (safePage - 1) * safeLimit

    const conditions = ['a.acted_by = ?']
    const params = [Number(teacherId)]

    // 如果指定了状态，则只查询该状态的记录
    if (status) {
      conditions.push('l.status = ?')
      params.push(status)
    }

    const whereClause = conditions.length ? 'WHERE ' + conditions.join(' AND ') : ''

    // 安全的排序字段白名单
    const allowedSortFields = ['created_at', 'start_at', 'end_at', 'hours', 'status', 'type', 'updated_at']
    const safeSortBy = allowedSortFields.includes(sortBy) ? sortBy : 'updated_at'
    const safeOrder = order.toUpperCase() === 'ASC' ? 'ASC' : 'DESC'

    // 查询总数 - 通过 approval_steps 表找出该教师审批过的记录
    const countSql = `
      SELECT COUNT(DISTINCT l.id) as total
      FROM leaves l
      INNER JOIN approval_steps a ON l.id = a.leave_id
      ${whereClause}
    `
    const countResult = await query(countSql, params)
    const total = countResult[0]?.total || 0

    // 查询列表
    const listSql = `
      SELECT DISTINCT l.*, u.name as student_name, u.username as student_username
      FROM leaves l
      LEFT JOIN users u ON l.student_id = u.id
      INNER JOIN approval_steps a ON l.id = a.leave_id
      ${whereClause}
      ORDER BY l.${safeSortBy} ${safeOrder}
      LIMIT ${safeLimit} OFFSET ${offset}
    `
    const list = await query(listSql, params)

    return {
      list,
      total,
      page: safePage,
      limit: safeLimit,
      totalPages: Math.ceil(total / safeLimit),
    }
  }

  /**
   * 获取统计数据
   * @returns {Promise<Object>}
   */
  static async getStats() {
    const sql = `
      SELECT
        COUNT(*) as total,
        SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending,
        SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) as approved,
        SUM(CASE WHEN status = 'rejected' THEN 1 ELSE 0 END) as rejected,
        SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled,
        SUM(CASE WHEN status = 'invalid' THEN 1 ELSE 0 END) as invalid,
        SUM(CASE WHEN type = 'sick' THEN 1 ELSE 0 END) as sick,
        SUM(CASE WHEN type = 'personal' THEN 1 ELSE 0 END) as personal,
        SUM(CASE WHEN type = 'other' THEN 1 ELSE 0 END) as other,
        SUM(hours) as totalHours
      FROM leaves
    `
    const rows = await query(sql, [])
    return rows[0]
  }
}
